<?php

class M_unitkerja extends CI_Model{
	
	function M_unitkerja(){
		parent::__construct();
	}
	
	function get_list(){
		/*$query = "SELECT a.KODEUNIT AS KODEUNIT, b.KODEUNIT AS P_KODEUNIT, b.NAMAUNIT AS P_NAMAUNIT, a.NAMAUNIT AS NAMAUNIT, a.LEAF AS LEAF
		FROM unitkerja a LEFT JOIN unitkerja b ON(b.KODEUNIT = a.P_KODEUNIT)";*/
		/*$query = "SELECT  CONCAT(REPEAT('    ', level - 1), CAST(hi.KODEUNIT AS CHAR)) AS treeitem, 
			hi.KODEUNIT, hi.NAMAUNIT, IF(hi.P_KODEUNIT='_', NULL, hi.P_KODEUNIT) AS P_KODEUNIT, b.NAMAUNIT AS P_NAMAUNIT, LEVEL, hi.LEAF
		FROM    (
		        SELECT  hierarchy_unitkerja(KODEUNIT) AS KODEUNIT, @level AS LEVEL
		        FROM    (
		                SELECT  @start_with := '_',
		                        @id := @start_with,
		                        @level := 0
		                ) vars, vu_unitkerja
		        WHERE   @id IS NOT NULL
		        ) ho
		JOIN    vu_unitkerja hi
		ON      hi.KODEUNIT = ho.KODEUNIT
		LEFT JOIN unitkerja b ON(b.KODEUNIT = hi.P_KODEUNIT)";*/
		/*$query = "SELECT  hi.KODEUNIT, hi.NAMAUNIT, IF(hi.P_KODEUNIT='_', NULL, hi.P_KODEUNIT) AS P_KODEUNIT, b.NAMAUNIT AS P_NAMAUNIT, LEVEL, hi.LEAF
		FROM    (
		        SELECT  hierarchy_unitkerja(KODEUNIT) AS KODEUNIT, @level AS LEVEL
		        FROM    (
		                SELECT  @start_with := '_',
		                        @id := @start_with,
		                        @level := 0
		                ) vars, unitkerja
		        WHERE   @id IS NOT NULL
		        ) ho
		JOIN    unitkerja hi
		ON      hi.KODEUNIT = ho.KODEUNIT
		LEFT JOIN unitkerja b ON(b.KODEUNIT = hi.P_KODEUNIT)";*/
		$query = "SELECT CONCAT( REPEAT('        ', COUNT(parent.NAMAUNIT) - 1), node.NAMAUNIT) AS NAMAUNIT_CONCAT, node.NAMAUNIT, node.KODEUNIT AS KODEUNIT, (COUNT(parent.NAMAUNIT) - 1) AS DEPTH
				FROM unitkerja AS node,
				unitkerja AS parent
				WHERE node.LFT BETWEEN parent.LFT AND parent.RGT
				GROUP BY node.KODEUNIT
				HAVING DEPTH=0";
		
		
		$result = $this->db->query($query);
		$nbrows = $result->num_rows();
		
		$jsonresult = "";
		$data = array();
		if($nbrows>0){
			$i=0;
			foreach($result->result() as $row){
				$data[$i] = $row;
				$i++;
			}
			return "{\"data\":" .json_encode($data). "}";
		} else {
			return "{\"data\":" .$jsonresult. "}";
		}
	}
	
	function get_jab_byunitkerja($kodeunit){
		$query = "SELECT KODEUNIT, KODEJAB, NAMAJAB, IF(HITUNGLEMBUR='Y', 1, 0) AS HITUNGLEMBUR,
			IF(KOMPENCUTI='Y', 1, 0) AS KOMPENCUTI
		FROM jabatan
		WHERE jabatan.KODEUNIT='".$kodeunit."'";
		
		$result = $this->db->query($query);
		$nbrows = $result->num_rows();
		
		$jsonresult = "";
		$data = array();
		if($nbrows>0){
			$i=0;
			foreach($result->result() as $row){
				$data[$i] = $row;
				$i++;
			}
			return "{\"data\":" .json_encode($data). "}";
		} else {
			return "{\"data\":" .$jsonresult. "}";
		}
	}
	
	function get_sub_byunitkerja($kodeunit, $depth){
		/*$query = "SELECT KODEUNIT, NAMAUNIT, LEAF
		FROM unitkerja
		WHERE unitkerja.P_KODEUNIT='".$kodeunit."'";*/
		/*$query = "SELECT node.NAMAUNIT, node.KODEUNIT AS KODEUNIT, (COUNT(parent.NAMAUNIT) - 1) AS DEPTH
				FROM unitkerja AS node,
				unitkerja AS parent
				WHERE node.LFT BETWEEN parent.LFT AND parent.RGT
				GROUP BY node.KODEUNIT
				HAVING DEPTH=0";*/
		$query = "SELECT node.NAMAUNIT, node.KODEUNIT, (COUNT(parent.NAMAUNIT) - 1) AS DEPTH
				FROM unitkerja AS node,
					unitkerja AS parent,
					unitkerja AS sub_parent,
					(
						SELECT node.NAMAUNIT, (COUNT(parent.NAMAUNIT) - 1) AS DEPTH
						FROM unitkerja AS node,
						unitkerja AS parent
						WHERE node.LFT BETWEEN parent.LFT AND parent.RGT
						AND node.KODEUNIT = '".$kodeunit."'
						GROUP BY node.NAMAUNIT
						ORDER BY node.LFT
					) AS sub_tree
				WHERE node.LFT BETWEEN parent.LFT AND parent.RGT
					AND node.LFT BETWEEN sub_parent.LFT AND sub_parent.RGT
					AND sub_parent.NAMAUNIT = sub_tree.NAMAUNIT
				GROUP BY node.NAMAUNIT
				HAVING DEPTH = (".$depth." + 1)
				ORDER BY node.LFT";
	
		$result = $this->db->query($query);
		$nbrows = $result->num_rows();
	
		$jsonresult = "";
		$data = array();
		if($nbrows>0){
			$i=0;
			foreach($result->result() as $row){
				$data[$i] = $row;
				$i++;
			}
			return "{\"data\":" .json_encode($data). "}";
		} else {
			return "{\"data\":" .$jsonresult. "}";
		}
	}
	
	function create($kodeunit, $p_kodeunit, $namaunit, $leaf, $rowsjab){
		//checking $rowsjab.kodejab ada yang sama?
		//Jika ADA ==> proses create data dihentikan
		//Jika TIDAK ADA ==> proses create data dilanjutkan
		$stack = array();
		//$equal_value = 0;
		if(sizeof($rowsjab)>0){
			for($i=0; $i<sizeof($rowsjab); $i++){
				if(in_array($rowsjab[$i]['KODEJAB'], $stack)) return "Kode Jabatan harus tidak sama!";
				else array_push($stack, $rowsjab[$i]['KODEJAB']);
			}
		}
		
		/*if($equal_value>0){
			return 0;
		}else{
			return 1;
		}*/
		
		$sql = "SELECT KODEUNIT FROM unitkerja WHERE KODEUNIT='".$kodeunit."'";
		$rs = $this->db->query($sql);
		if($rs->num_rows()>0){
			return 0;
		}else{
			$data = array(
					"KODEUNIT"=>$kodeunit,
					"NAMAUNIT"=>$namaunit,
					"LEAF"=>$leaf
			);
			//if($p_kodeunit!='') $data['P_KODEUNIT'] = $p_kodeunit;
			
			$this->db->insert('unitkerja', $data);
			if($this->db->affected_rows()){
				$data_p_kodeunit = array('P_KODEUNIT'=>$p_kodeunit);
				$this->db->where('KODEUNIT', $kodeunit);
				$this->db->update('unitkerja', $data_p_kodeunit);
				
				//delete db.jabatan where kodeunit=$kodeunit
				$this->db->where('KODEUNIT', $kodeunit);
				$this->db->delete('jabatan');
				//insert jabatan
				//$this->firephp->log($rowsjab);
				for($j=0; $j<sizeof($rowsjab); $j++){
					$sqli_jab = "INSERT INTO jabatan (jabatan.KODEUNIT, jabatan.KODEJAB, jabatan.NAMAJAB, jabatan.HITUNGLEMBUR, jabatan.KOMPENCUTI)
					VALUES('".$kodeunit."', '".$rowsjab[$j]['KODEJAB']."', '".$rowsjab[$j]['NAMAJAB']."', 
					'".($rowsjab[$j]['HITUNGLEMBUR'] == 1 ? 'Y' : 'T')."', '".($rowsjab[$j]['KOMPENCUTI'] == 1 ? 'Y' : 'T')."')";
					$this->db->query($sqli_jab);
				}
				return 1;
			}else{
				return 0;
			}
		}
	}
	
	function update($kodeunit, $namaunit, $leaf, $rowsjab){
		//checking $rowsjab.kodejab ada yang sama?
		//Jika ADA ==> proses create data dihentikan
		//Jika TIDAK ADA ==> proses create data dilanjutkan
		$stack = array();
		//$equal_value = 0;
		if(sizeof($rowsjab)>0){
			for($i=0; $i<sizeof($rowsjab); $i++){
				if(in_array($rowsjab[$i]['KODEJAB'], $stack)) return "Kode Jabatan harus tidak sama!";
				else array_push($stack, $rowsjab[$i]['KODEJAB']);
			}
		}
		
		/*$data = array(
				"NAMAUNIT"=>$namaunit,
				"LEAF"=>$leaf
				);
		$this->db->where('KODEUNIT', $kodeunit);
		$this->db->update('unitkerja');*/
		$sqlu = "UPDATE unitkerja SET unitkerja.NAMAUNIT='".$namaunit."', unitkerja.LEAF='".$leaf."'
		WHERE unitkerja.KODEUNIT='".$kodeunit."'";
		$this->db->query($sqlu);
		
		//delete db.jabatan where kodeunit=$kodeunit
		$this->db->where('KODEUNIT', $kodeunit);
		$this->db->delete('jabatan');
		//insert jabatan
		//$this->firephp->log($rowsjab);
		for($j=0; $j<sizeof($rowsjab); $j++){
			$sqli_jab = "INSERT INTO jabatan (jabatan.KODEUNIT, jabatan.KODEJAB, jabatan.NAMAJAB, jabatan.HITUNGLEMBUR, jabatan.KOMPENCUTI)
			VALUES('".$kodeunit."', '".$rowsjab[$j]['KODEJAB']."', '".$rowsjab[$j]['NAMAJAB']."',
			'".($rowsjab[$j]['HITUNGLEMBUR'] == 'true' ? 'Y' : 'T')."', '".($rowsjab[$j]['KOMPENCUTI'] == 'true' ? 'Y' : 'T')."')";
			$this->db->query($sqli_jab);
		}
		
		
		return 1;
	}
	
	
}

?>